package cn.xxy;

import cn.xxy.beans.DataSourceConfig;
import cn.xxy.datasource.DataSourceFactory;
import cn.xxy.datasource.NoneDataSourceAdapter;
import cn.xxy.exception.CloseResourceException;
import cn.xxy.exception.DataAccessException;
import cn.xxy.exception.ExecuteParamsException;
import cn.xxy.result.ColumnHandler;
import cn.xxy.utils.ConnUtils;
import org.apache.commons.dbutils.OutParameter;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;

import javax.sql.DataSource;
import java.sql.*;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

public class DbHelper extends QueryRunner {
	protected final DataSourceConfig dsc;
	//protected final DataSource ds;
	/**
	 * 从构造方法传入一个连接对象
	 * @param dsc 连接对象源配置
	 */
	public DbHelper(DataSourceConfig dsc) {
        super(DataSourceFactory.getDataSource(dsc));
		this.dsc = dsc;
	}
	/**
	 * 从构造方法传入一个连接对象
	 * @param dbName 连接对象名
	 */
	public DbHelper(String dbName){
		super(DataSourceFactory.getDataSource(ConnUtils.getDataSourceConfig(dbName)));
		this.dsc = ConnUtils.getDataSourceConfig(dbName);
	}

	/**
	 * 查询单个对象
	 * @param sql sql 语句
	 * @param errorObj 异常时返回此对象
	 * @param params sql 参数
	 * @return 异常时返回 <code>errorObj</code>
	 */
	public <T> T queryObject(String sql, T errorObj, Object... params) throws SQLException{
		return queryObject(getConnection(), sql, params);
	}
	/**
	 * 查询单个对象
	 * @param conn 数据库连接
	 * @param sql sql 语句
	 * @param params sql 参数
	 */
	public <T> T queryObject(Connection conn, String sql, Object... params) throws SQLException {
		return query(conn, sql, new ScalarHandler<T>(), params);
	}
	/**
	 * 查询第一列数据
	 * @param sql sql 语句
	 * @param params sql 参数
	 * @return 异常时返回 <code>null</code>
	 */
	public <T> List<T> queryColList(String sql, Object... params) throws SQLException {
		return queryColList(getConnection(), sql, params);
	}

	/**
	 * 查询第一列数据
	 * @param conn 数据库连接
	 * @param sql sql 语句
	 * @param params sql 参数
	 */
	public <T> List<T> queryColList(Connection conn, String sql, Object... params) throws SQLException {
		return query(conn, sql, new cn.xxy.result.ColumnListHandler<T>(), params);
	}

	/**
	 * 查询第一行数据并封装为实体类返回
	 * @param sql sql 语句
	 * @param clazz 实体类型
	 * @param errorObj 异常时返回此对象
	 * @param params sql 参数
	 * @return 异常时返回 <code>errorObj</code>
	 */
	public <T> T queryBean(String sql, Class<T> clazz, T errorObj, Object... params) throws SQLException {
		return queryBean(getConnection(), sql, clazz, params);
	}

	/**
	 * 查询第一行数据并封装为实体类返回
	 * @param conn 数据库连接
	 * @param sql sql 语句
	 * @param clazz 实体类型
	 * @param params sql 参数
	 */
	public <T> T queryBean(Connection conn, String sql, Class<T> clazz, Object... params) throws SQLException {
			return query(conn, sql, new cn.xxy.result.BeanHandler<T>(clazz), params);
	}

	/**
	 * 查询数据并封装为 <code>List&lt;T&gt;</code> 返回
	 * @param sql sql 语句
	 * @param clazz 实体类型
	 * @param params sql 参数
	 * @return 异常时返回 <code>null</code>
	 */
	public <T> List<T> queryListBean(String sql, Class<T> clazz, Object... params) throws SQLException{
		return queryListBean(getConnection(), sql, clazz, params);
	}

	/**
	 * 查询数据并封装为 <code>List&lt;T&gt;</code> 返回
	 * @param conn 数据库连接
	 * @param sql sql 语句
	 * @param clazz 实体类型
	 * @param params sql 参数
	 */
	public <T> List<T> queryListBean(Connection conn, String sql, Class<T> clazz, Object... params) throws SQLException {
		return query(conn, sql, new cn.xxy.result.BeanListHandler<T>(clazz), params);
	}

	/**
	 * 查询第一行数据并封装为 <code>Object[]</code> 返回
	 * @param sql sql 语句
	 * @param params sql 参数
	 * @return 异常时返回 <code>null</code>，无数据时返回空数组（<code>new Object[0]</code>）
	 */
	public Object[] queryRowArray(String sql, Object... params) throws SQLException {
		return queryRowArray(getConnection(), sql, params);
	}

	/**
	 * 查询第一行数据并封装为 <code>Object[]</code> 返回
	 * @param conn 数据库连接
	 * @param sql sql 语句
	 * @param params sql 参数
	 * @return 无数据时返回空数组（<code>new Object[0]</code>）
	 */
	public Object[] queryRowArray(Connection conn, final String sql, Object... params) throws SQLException {
			Object[] result = query(conn, sql, new ArrayHandler(), params);
			return result == null ? new Object[0] : result;
	}

	/**
	 * 查询第一行数据并封装为 <code>Map&lt;String, Object&gt;</code> 返回
	 * @param sql sql 语句
	 * @param params sql 参数
	 * @return 异常时返回 <code>null</code>，无数据时返回空Map（<code>new HashMap&lt;&gt;()</code>）
	 */
	public Map<String, Object> queryRowMap(String sql, Object... params) throws SQLException {
		return queryRowMap(getConnection(), sql, params);
	}

	/**
	 * 查询第一行数据并封装为 <code>Map&lt;String, Object&gt;</code> 返回
	 * @param conn 数据库连接
	 * @param sql sql 语句
	 * @param params sql 参数
	 * @return 无数据时返回空Map（<code>new HashMap&lt;&gt;()</code>）
	 */
	public Map<String, Object> queryRowMap(Connection conn, String sql, Object... params) throws SQLException {
			Map<String, Object> result = query(conn, sql, new MapHandler(), params);
			return result == null ? new HashMap<String, Object>() : result;
	}

	/**
	 * 查询数据并封装为 <code>List&lt;Map&lt;String, Object&gt;&gt;</code> 返回
	 * @param sql sql 语句
	 * @param params sql 参数
	 * @return 异常时返回 <code>null</code>
	 */
	public List<Map<String, Object>> queryListMap(String sql, Object... params) throws SQLException{
		return queryListMap(getConnection(), sql, params);
	}

	/**
	 * 查询数据并封装为 <code>List&lt;Map&lt;String, Object&gt;&gt;</code> 返回
	 * @param conn 数据库连接
	 * @param sql sql 语句
	 * @param params sql 参数
     */
	public List<Map<String, Object>> queryListMap(Connection conn, String sql, Object... params) throws SQLException {
			return query(conn, sql, new MapListHandler(), params);
	}

	/**
	 * 查询数据并封装为 <code>List&lt;Object[]&gt;</code> 返回
	 * @param sql sql 语句
	 * @param params sql 参数
	 * @return 异常时返回 <code>null</code>
	 */
	public List<Object[]> queryListArray(String sql, Object... params) throws SQLException{
		return queryListArray(getConnection(), sql, params);
	}

	/**
	 * 查询数据并封装为 <code>List&lt;Object[]&gt;</code> 返回
	 * @param conn 数据库连接
	 * @param sql sql 语句
	 * @param params sql 参数
     */
	public List<Object[]> queryListArray(Connection conn, String sql, Object... params) throws SQLException {
			return query(conn, sql, new ArrayListHandler(), params);
	}


	/**
	 * 查询数据并封装为 <code>Map&lt;K, Map&lt;String, Object&gt;&gt;</code> 返回
	 * @param sql sql 语句
	 * @param keyedCol 键列列名
	 * @param params sql 参数
	 * @return 异常时返回 <code>null</code>，无数据时返回空Map（<code>new HashMap&lt;&gt;()</code>）
	 */
	public <K> Map<K, Map<String, Object>> queryKeyedMap(String sql, String keyedCol, Object... params) throws SQLException{
		return queryKeyedMap(getConnection(), sql, keyedCol, params);
	}

	/**
	 * 查询数据并封装为 <code>Map&lt;K, Map&lt;String, Object&gt;&gt;</code> 返回
	 * @param conn 数据库连接
	 * @param sql sql 语句
	 * @param keyedCol 键列列名
	 * @param params sql 参数
	 * @return 无数据时返回空Map（<code>new HashMap&lt;&gt;()</code>）
     */
	public <K> Map<K, Map<String, Object>> queryKeyedMap(Connection conn, String sql, String keyedCol, Object... params) throws SQLException {
			Map<K, Map<String, Object>> result = query(conn, sql, new KeyedHandler<K>(keyedCol), params);
			return result == null ? new HashMap<K, Map<String, Object>>() : result;
	}

	/**
	 * 查询数据并封装为 <code>Map&lt;K, B&gt;</code> 返回
	 * @param sql sql 语句
	 * @param clazz 实体类型
	 * @param keyedCol 键列列名
	 * @param params sql 参数
	 * @return 异常时返回 <code>null</code>，无数据时返回空Map（<code>new HashMap&lt;&gt;()</code>）
	 */
	public <K, B> Map<K, B> queryKeyedBean(String sql, Class<B> clazz, String keyedCol, Object... params) throws SQLException{
		return queryKeyedBean(getConnection(), sql, clazz, keyedCol, params);
	}

	/**
	 * 查询数据并封装为 <code>Map&lt;K, B&gt;</code> 返回
	 * @param conn 数据库连接
	 * @param sql sql 语句
	 * @param clazz 实体类型
	 * @param keyedCol 键列列名
	 * @param params sql 参数
	 * @return 无数据时返回空Map（<code>new HashMap&lt;&gt;()</code>）
     */
	public <K, B> Map<K, B> queryKeyedBean(Connection conn, String sql, Class<B> clazz, String keyedCol, Object... params) throws SQLException {
		Map<K, B> result = query(conn, sql, new cn.xxy.result.BeanMapHandler<K, B>(clazz, keyedCol), params);
		return result == null ? new HashMap<K, B>() : result;
	}
	//--------------------------------------------------------------------------------------------------------------------------------------------------
	/**
	 * 查询第一行数据并封装为实体类返回
	 * @param sql sql 语句或存储过程
	 * @param clazz 实体类型
	 * @param errorObj 异常时返回此对象
	 * @param params sql 参数
	 * @return 异常时返回 <code>errorObj</code>
	 */
	public <T> T execQueryBean(String sql, Class<T> clazz, T errorObj, Object... params) throws SQLException {
		return execQueryBean(getConnection(), sql, clazz, params);
	}

	/**
	 * 查询第一行数据并封装为实体类返回
	 * @param conn 数据库连接
	 * @param sql sql 语句或存储过程
	 * @param clazz 实体类型
	 * @param params sql 参数
	 */
	public  <T> T execQueryBean(Connection conn, String sql, Class<T> clazz, Object... params) throws SQLException{
		List<T> result = execute(conn, sql, new cn.xxy.result.BeanHandler<T>(clazz), params);
		return result == null || result.isEmpty() ? null : result.get(0);
	}
	/**
	 * 查询数据并封装为 <code>List&lt;T&gt;</code> 返回
	 * @param sql sql 语句或存储过程
	 * @param clazz 实体类型
	 * @param params sql 参数
	 * @return 异常时返回 <code>null</code>
	 */
	public <T> List<T> execQueryListBean(String sql, Class<T> clazz, Object... params) throws SQLException{
		return execQueryListBean(getConnection(), sql, clazz, params);
	}

	/**
	 * 查询数据并封装为 <code>List&lt;T&gt;</code> 返回
	 * @param conn 数据库连接
	 * @param sql sql 语句或存储过程
	 * @param clazz 实体类型
	 * @param params sql 参数
	 */
	public <T> List<T> execQueryListBean(Connection conn, String sql, Class<T> clazz, Object... params) throws SQLException {
		return execute(conn, sql, new cn.xxy.result.BeanHandler<T>(clazz), params);
	}

	/**
	 * 查询第一行数据并封装为 <code>Map&lt;String, Object&gt;</code> 返回
	 * @param conn 数据库连接
	 * @param sql sql 语句或存储过程
	 * @param params sql 参数
	 * @return 无数据时返回空Map（<code>new HashMap&lt;&gt;()</code>）
	 */
	public Map<String, Object> execQueryRowMap(Connection conn, String sql, Object... params) throws SQLException {
		List<Map<String, Object>> result = execute(conn, sql, new MapHandler(), params);
		return result == null || result.isEmpty() ? new HashMap<String, Object>() : result.get(0);
	}

	/**
	 * 查询数据并封装为 <code>List&lt;Map&lt;String, Object&gt;&gt;</code> 返回
	 * @param sql sql 语句或存储过程
	 * @param params sql 参数
	 * @return 异常时返回 <code>null</code>
	 */
	public List<Map<String, Object>> execQueryListMap(String sql, Object... params) throws SQLException{
		return execQueryListMap(getConnection(), sql, params);
	}

	/**
	 * 查询数据并封装为 <code>List&lt;Map&lt;String, Object&gt;&gt;</code> 返回
	 * @param conn 数据库连接
	 * @param sql sql 语句或存储过程
	 * @param params sql 参数
	 */
	public List<Map<String, Object>> execQueryListMap(Connection conn, String sql, Object... params) throws SQLException {
		return  execute(conn, sql, new MapHandler(), params);
	}
	//--------------------------------------------------------------------------------------------------------------------------------------------------
	/**
	 * Executes the given INSERT SQL statement. The
	 * {@code Connection} is retrieved from the {@code DataSource}
	 * set in the constructor.  This {@code Connection} must be in
	 * auto-commit mode or the insert will not be saved.
	 * @param <T> The type of object that the handler returns
	 * @param sql The SQL statement to execute.
	 * the {@code ResultSet} of auto-generated keys.
	 * @param params Initializes the PreparedStatement's IN (i.e. '?')
	 * @return An object generated by the handler.
	 * @throws SQLException if a database access error occurs
	 * @since 1.6
	 */
	public <T> T insert(final String sql,  final Object... params) throws SQLException {
		try (Connection conn = getConnection()) {
			return insert(conn, sql, new ColumnHandler<T>(1), params);
		}
	}
	//=======================================================================================================================================
	/**
	 * Execute an SQL SELECT query with replacement parameters.  The
	 * caller is responsible for closing the connection.
	 * @param <T> The type of object that the handler returns
	 * @param conn The connection to execute the query in.
	 * @param sql The query to execute.
	 * @param rsh The handler that converts the results into an object.
	 * @param params The replacement parameters.
	 * @return The object returned by the handler.
	 * @throws SQLException if a database access error occurs
	 */
	@Override
	public <T> T query(final Connection conn, final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException {
		try {
			return super.query(conn, sql,rsh, params);
		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			if (conn.getAutoCommit()) {
				closeConns(conn);
			}
		}
	}
	/**
	 * Execute an SQL statement, including a stored procedure call, which does
	 * not return any result sets.
	 * Any parameters which are instances of {@link OutParameter} will be
	 * registered as OUT parameters.
	 * <p>
	 * Use this method when invoking a stored procedure with OUT parameters
	 * that does not return any result sets.  If you are not invoking a stored
	 * procedure, or the stored procedure has no OUT parameters, consider using
	 * {@link #update(java.sql.Connection, String, Object...) }.
	 * If the stored procedure returns result sets, use
	 * {@link #execute(java.sql.Connection, String, org.apache.commons.dbutils.ResultSetHandler, Object...) }.
	 *
	 * @param conn The connection to use to run the query.
	 * @param sql The SQL to execute.
	 * @param params The query replacement parameters.
	 * @return The number of rows updated.
	 * @throws SQLException if a database access error occurs
	 */
	@Override
	public int execute(final Connection conn, final String sql, final Object... params) throws SQLException {
		try {
			return super.execute(conn, sql, params);
		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			if (conn.getAutoCommit()) {
				closeConns(conn);
			}
		}
	}

	/**
	 * Execute an SQL statement, including a stored procedure call, which
	 * returns one or more result sets.
	 * Any parameters which are instances of {@link OutParameter} will be
	 * registered as OUT parameters.
	 * <p>
	 * Use this method when: a) running SQL statements that return multiple
	 * result sets; b) invoking a stored procedure that return result
	 * sets and OUT parameters.  Otherwise you may wish to use
	 * {@link #query(java.sql.Connection, String, org.apache.commons.dbutils.ResultSetHandler, Object...) }
	 * (if there are no OUT parameters) or
	 * {@link #execute(java.sql.Connection, String, Object...) }
	 * (if there are no result sets).
	 *
	 * @param <T> The type of object that the handler returns
	 * @param conn The connection to use to run the query.
	 * @param sql The SQL to execute.
	 * @param rsh The result set handler
	 * @param params The query replacement parameters.
	 * @return A list of objects generated by the handler
	 * @throws SQLException if a database access error occurs
	 */
	@Override
	public <T> List<T> execute(final Connection conn, final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException {
		try {
			return super.execute(conn, sql, rsh, params);
		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			if (conn.getAutoCommit()) {
				closeConns(conn);
			}
		}
	}
	/**
	 * Execute an SQL INSERT query.
	 * @param <T> The type of object that the handler returns
	 * @param conn The connection to use to run the query.
	 * @param sql The SQL to execute.
	 * @param rsh The handler used to create the result object from
	 * the {@code ResultSet} of auto-generated keys.
	 * @param params The query replacement parameters.
	 * @return An object generated by the handler.
	 * @throws SQLException if a database access error occurs
	 * @since 1.6
	 */
	@Override
	public <T> T insert(final Connection conn, final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException {
		try {
			return super.insert(conn, sql, rsh,params);
		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			if (conn.getAutoCommit()) {
				closeConns(conn);
			}
		}
	}
	/**
	 * Execute an SQL INSERT, UPDATE, or DELETE query.
	 *
	 * @param conn The connection to use to run the query.
	 * @param sql The SQL to execute.
	 * @param params The query replacement parameters.
	 * @return The number of rows updated.
	 * @throws SQLException if a database access error occurs
	 */
	@Override
	public int update(final Connection conn, final String sql, final Object... params) throws SQLException {
		try {
			return super.update(conn, sql, params);
		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			if (conn.getAutoCommit()) {
				closeConns(conn);
			}
		}
	}
	/**
	 * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
	 *
	 * @param conn The Connection to use to run the query.  The caller is
	 * responsible for closing this Connection.
	 * @param sql The SQL to execute.
	 * @param params An array of query replacement parameters.  Each row in
	 * this array is one set of batch replacement values.
	 * @return The number of rows updated per statement.
	 * @throws SQLException if a database access error occurs
	 * @since 1.1
	 */
	@Override
	public int[] batch(final Connection conn, final String sql, final Object[][] params) throws SQLException {
		try {
			return super.batch(conn, sql, params);
		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			if (conn.getAutoCommit()) {
				closeConns(conn);
			}
		}
	}
	/**
	 * Executes the given batch of INSERT SQL statements.
	 * @param <T> The type of object that the handler returns
	 * @param conn The connection to use to run the query.
	 * @param sql The SQL to execute.
	 * @param rsh The handler used to create the result object from
	 * the {@code ResultSet} of auto-generated keys.
	 * @param params The query replacement parameters.
	 * @return The result generated by the handler.
	 * @throws SQLException if a database access error occurs
	 * @since 1.6
	 */
	@Override
	public <T> T insertBatch(final Connection conn, final String sql, final ResultSetHandler<T> rsh, final Object[][] params) throws SQLException {
		try {
			return super.insertBatch(conn, sql,rsh, params);
		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			if (conn.getAutoCommit()) {
				closeConns(conn);
			}
		}
	}
	//=======================================================================================================================================
	/**
	 * 获取数据库连接
	 * 自动提交事务
	 */
	public Connection getConnection() throws SQLException {
		return getConnection(true);
	}
	/**
	 * 获取数据库连接
	 * @param autoCommit 是否自动提交事务
	 */
	public final synchronized Connection getConnection(boolean autoCommit) throws SQLException {
		if (dsc == null) {
			throw new RuntimeException("没有读取到对应的配置信息");
		}
		if (dsc.getDriverClass().isEmpty()) {
			throw new RuntimeException("没有读取到对应的配置信息for:【" + dsc.getDbName() + "】");
		}
		try {//加载驱动
			Class.forName(dsc.getDriverClass());
			org.apache.commons.dbutils.DbUtils.loadDriver(dsc.getDriverClass());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection conn = super.prepareConnection() ;//ds.getConnection();
		conn.setAutoCommit(autoCommit);
		return conn;
	}

	/**
	 * 开启事务
	 */
	public Connection beginTransaction() throws SQLException {
		return getConnection(false);
	}
	/**
	 * 回滚数据库连接
     */
	public void rollbackConns(Connection... conns) {
		if (conns != null) {
			for (Connection conn : conns) {
				try {
					conn.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 提交数据库连接
	 */
	public void commitConns(Connection... conns) {
		if (conns != null) {
			for (Connection conn : conns) {
				try {
					conn.commit();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 关闭数据库连接
	 */
	public void closeConns(Connection... conns) {
		if (conns != null) {
			for (Connection conn : conns) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	//=======================================================================================================================================
//	/**
//	 * 关闭ResultSet
//	 */
//	protected void close(ResultSet... rss) {
//		if (rss != null) {
//			for (ResultSet rs : rss) {
//				try {
//					rs.close();
//				} catch (SQLException e) {
//					e.printStackTrace();
//				}
//			}
//		}
//	}
//
//	/**
//	 * 关闭Statement
//	 */
//	protected void close(Statement... sts) {
//		if (sts != null) {
//			try {
//				for (Statement st : sts) {
//					st.close();
//				}
//			} catch (SQLException e) {
//				throw new CloseResourceException("Close statement fail.", e);
//			}
//		}
//	}
	/**
	 * 设置sql语句的参数
	 * @param ps PreparedStatement
	 * @param params 执行参数
	 */
	protected void setParameters(PreparedStatement ps, Object[] params) throws SQLException{
		if (params == null || params.length == 0) {
			return;
		}
		for(int i = 0; i < params.length; i++) {
			ps.setObject(i + 1, params[i]);
		}
	}
}
